import pandas as pd
import numpy as np
from scipy.stats import zscore

# 参数配置
input_file = "Training_Samples.xlsx"
output_file = "output2.xlsx"
anomalies_file = "anomalies.xlsx"  # 新增异常值输出文件
z_threshold = 3
class_list = [1, 2, 4, 5, 6, 7]

# 读取数据
df = pd.read_excel(input_file)

# 获取数值型列
numerical_cols = df.select_dtypes(include=[np.number]).columns.tolist()
numerical_cols.remove("CLASS")

# 初始化存储
filtered_dfs = []
outliers_list = []

# 按类别处理
for class_val in class_list:
    class_data = df[df["CLASS"] == class_val].copy()
    if len(class_data) == 0:
        continue

    # 计算Z分数
    z_scores = class_data[numerical_cols].apply(zscore)

    # 标记异常
    outliers_mask = (z_scores.abs() > z_threshold).any(axis=1)
    class_outliers = class_data[outliers_mask]

    # 记录异常值
    if not class_outliers.empty:
        print(f"\n=== 类别 {class_val} 异常样本 ===")
        print(class_outliers.to_string(index=False))
        outliers_list.append(class_outliers)

    # 保留正常样本
    filtered_class = class_data[~outliers_mask]
    filtered_dfs.append(filtered_class)

# 合并结果
if filtered_dfs:
    result = pd.concat(filtered_dfs, ignore_index=True)
    result.to_excel(output_file, index=False)

    # 处理异常值
    if outliers_list:
        total_outliers = pd.concat(outliers_list)

        # 导出异常值到单独文件
        total_outliers.to_excel(anomalies_file, index=False)
        print(f"\n异常值已导出到：{anomalies_file}")

        # 控制台汇总
        print("\n=== 异常值汇总 ===")
        print(f"总异常样本数：{len(total_outliers)}")
        print(f"各类别异常分布：\n{total_outliers['CLASS'].value_counts().to_string()}")
    else:
        print("\n未检测到异常值")

    print(f"\n处理完成，保留样本数：{len(result)}，原始样本数：{len(df)}")
else:
    print("未找到有效数据")